package com.napenalki.toto.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import javax.sql.DataSource;

import org.junit.Before;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.napenalki.toto.domain.Match;
import com.napenalki.toto.domain.Prediction;
import com.napenalki.toto.domain.SpecialEvent;
import com.napenalki.toto.domain.SpecialEvents;
import com.napenalki.toto.domain.Stages;
import com.napenalki.toto.domain.User;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/META-INF/spring/applicationContext.xml" })
public abstract class DaoTestFixture {

    @Autowired
    private DataSource ds;
    private Connection con;

    @Before
    public void populateDatabase() throws SQLException {
        cleanupPredictions();
        cleanupMatches();
        cleanupSpecialEvents();
        cleanupUsers();
        populateUsers();
        populateSpecialEvents();
        populateMatches();
        populatePredictions();
    }

    private void populateUsers() throws SQLException {
        insertUser(new User("ivan_stefanov", "ivan_stefanov",
                "ivan_stefanov@napenalki.com", "Ivan St. Ivanov", 0));
        insertUser(new User("raycho_hip", "raycho_hip",
                "raycho_hip@napenalki.com", "Raycho Nikov", 1));
        insertUser(new User("nikola_osv", "nikola_osv",
                "nikola_osv@napenalki.com", "Nikola Nikov", 2));
        insertUser(new User("loritage", "loritage", "loritage@napenalki.com",
                "Yolita Ivanova", 3));
    }

    protected void cleanupUsers() throws SQLException {
        cleanupPredictions();
        PreparedStatement stmt = getConnection().prepareStatement(
                "DELETE FROM USER");
        stmt.executeUpdate();
        stmt.close();
    }

    protected void insertUser(User user) throws SQLException {
        PreparedStatement stmt = getConnection()
                .prepareStatement(
                        "INSERT INTO USER (USER_NAME, PASSWORD, EMAIL, DISPLAY_NAME, POINTS) VALUES (?, ?, ?, ?, ?)");
        stmt.setString(1, user.getUserName());
        stmt.setString(2, user.getPassword());
        stmt.setString(3, user.getEmail());
        stmt.setString(4, user.getDisplayName());
        stmt.setInt(5, user.getPoints());
        stmt.execute();
        stmt.close();
    }

    private void populateSpecialEvents() throws SQLException {
        /*
         * The IDs should be large numbers. The problem is that the entity
         * manager creates the rows with IDs like 1, 2 and so forth without
         * checking before that that they are caught
         */
        insertSpecialEvent(new SpecialEvent(10001, new Date(System
                .currentTimeMillis()), false, null, SpecialEvents.BEST_SCORER));
        insertSpecialEvent(new SpecialEvent(20002, new Date(System
                .currentTimeMillis() + 1000), false, null,
                SpecialEvents.FINALISTS));
    }

    protected void cleanupSpecialEvents() throws SQLException {
        cleanupPredictions();
        Connection con = getConnection();
        PreparedStatement stmt = con
                .prepareStatement("DELETE FROM SPECIAL_EVENT");
        stmt.executeUpdate();
    }

    protected void insertSpecialEvent(SpecialEvent event) throws SQLException {
        Connection con = getConnection();
        PreparedStatement stmt = con
                .prepareStatement("INSERT INTO SPECIAL_EVENT (ID, EVENT_DATE, LOCKED, RESULT, EVENT_DETAILS) VALUES(?, ?, ?, ?, ?)");
        stmt.setInt(1, event.getId());
        stmt.setTimestamp(2, new Timestamp(event.getDate().getTime()));
        stmt.setBoolean(3, event.isLocked());
        stmt.setString(4, event.getResult());
        stmt.setString(5, event.getEventType().toString());
        stmt.executeUpdate();
    }

    private void populateMatches() throws SQLException {
        insertMatch(new Match(888, new Date(System.currentTimeMillis()), false,
                null, "England", "Argentina", Stages.QUARTERFINALS));
        insertMatch(new Match(889, new Date(System.currentTimeMillis()), false,
                null, "France", "Brazil", Stages.SEMIFINALS));
        insertMatch(new Match(890, new Date(System.currentTimeMillis()), false,
                null, "Netherlands", "Cameroon", Stages.GROUP_E));
    }

    protected void cleanupMatches() throws SQLException {
        cleanupPredictions();
        PreparedStatement stmt = getConnection().prepareStatement(
                "DELETE FROM MATCH_EVENT");
        stmt.executeUpdate();
    }

    protected void insertMatch(Match testMatch) throws SQLException {
        PreparedStatement stmt = getConnection()
                .prepareStatement(
                        "INSERT INTO MATCH_EVENT (ID, EVENT_DATE, LOCKED, RESULT, STAGE, TEAM1, TEAM2) VALUES (?, ?, ?, ?, ?, ?, ?)");
        stmt.setInt(1, testMatch.getId());
        stmt.setTimestamp(2, new Timestamp(testMatch.getDate().getTime()));
        stmt.setBoolean(3, testMatch.isLocked());
        stmt.setString(4, testMatch.getResult());
        stmt.setString(5, testMatch.getStage().toString());
        stmt.setString(6, testMatch.getTeam1Name());
        stmt.setString(7, testMatch.getTeam2Name());
        stmt.executeUpdate();
    }

    private void populatePredictions() throws SQLException {
        insertPrediction(new Prediction(33033, new User("ivan_stefanov",
                "ivan_stefanov", "ivan_stefanov@napenalki.com",
                "Ivan St. Ivanov", 0), new Match(888, new Date(System
                .currentTimeMillis()), false, null, "England", "Argentina",
                Stages.QUARTERFINALS), "1:1"));
        insertPrediction(new Prediction(33034, new User("loritage", "loritage",
                "loritage@napenalki.com", "Yolita Ivanova", 3),
                new SpecialEvent(10001, new Date(System.currentTimeMillis()),
                        false, null, SpecialEvents.BEST_SCORER), "Rooney"));
        insertPrediction(new Prediction(33035, new User("nikola_osv",
                "nikola_osv", "nikola_osv@napenalki.com", "Nikola Nikov", 2),
                new Match(889, new Date(System.currentTimeMillis()), false,
                        null, "France", "Brazil", Stages.SEMIFINALS), "0:2"));
    }

    protected void cleanupPredictions() throws SQLException {
        PreparedStatement stmt = getConnection().prepareStatement(
                "DELETE FROM PREDICTION");
        stmt.executeUpdate();
    }

    protected void insertPrediction(Prediction prediction) throws SQLException {
        PreparedStatement stmt = getConnection()
                .prepareStatement(
                        "INSERT INTO PREDICTION (ID, USER_NAME, EVENT_ID, PREDICTION) VALUES (?, ?, ?, ?)");
        stmt.setInt(1, prediction.getId());
        stmt.setString(2, prediction.getUser().getUserName());
        stmt.setInt(3, prediction.getEvent().getId());
        stmt.setString(4, prediction.getPrediction());
        stmt.executeUpdate();
    }

    protected Connection getConnection() throws SQLException {
        if (con == null) {
            con = ds.getConnection();
        }

        return con;
    }

    protected User createTestUser() {
        return new User("testUser", "testPassword", "test@napenalki.com",
                "test", 0);
    }

    protected Match createTestMatch() {
        return new Match(new Date(System.currentTimeMillis()), false, null,
                "Portugal", "Brazil", Stages.GROUP_G);
    }

}
